package com.cn.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Transaction;

import com.cn.bean.Provider;
import com.cn.bean.Provider_servicecatalog;
import com.cn.bean.Servicecatalog;
import com.cn.dao.ServicecatalogDAO;

public class ServicecatalogDAOImpl extends  BaseHibernateDAOImpl implements ServicecatalogDAO{

	//根据服务种类的name查找服务目录
	public List getSCbyST(String st_name){
		try {
			String queryString = "from Servicecatalog as sc where servicetype.name='"+st_name+"'";
			getSession().beginTransaction();
			Query queryObject = getSession().createQuery(queryString);
			List list=queryObject.list();
			getSession().getTransaction().commit();
			return list;
		} catch (RuntimeException re) {
			throw re;
		}
	}
	/**
	 * 根据id查找服务目录
	 * @param id
	 * @return 服务目录对象
	 */
	public Object findById(int id){
		try {
			String queryString = "from Servicecatalog as sc where id ="+id;
			getSession().beginTransaction();
			Query queryObject = getSession().createQuery(queryString);
			List list = queryObject.list();
			getSession().getTransaction().commit();
			if (list.size() == 0) {
				return null;
			}
			return list.get(0);
		} catch (RuntimeException re) {
			throw re;
		}
	}
	/**
	 * 返回某种类下的所有目录
	 */
	public List listAllCatalog(int id)
	{
		try {
			String queryString = "from Servicecatalog as st where st_id='"+String.valueOf(id)+"'";
			getSession().beginTransaction();
			Query queryObject = getSession().createQuery(queryString);
			List list=queryObject.list();
			getSession().getTransaction().commit();
			return list;
			} catch (RuntimeException re) {
				throw re;
			}
	}
	
	public boolean deleteProviderCatalog(int pro_id,int sc_id)
	{
		try {
			 String hql = " from Provider_servicecatalog as psc where pro_id="+pro_id+" and sc_id="+sc_id;  
			 getSession().beginTransaction();
			 Query queryObject = getSession().createQuery(hql);
			 List <Object>list = queryObject.list();
			 getSession().getTransaction().commit();
			 Provider_servicecatalog psc=(Provider_servicecatalog)list.get(0);
		     
			return delete(psc);
			} catch (RuntimeException re) {
				throw re;
				
			}
		
	}
	
	public boolean changeProviderCatalog(int pro_id, int sc_id, float price) {
		try {
			 String hql = " from Provider_servicecatalog as psc where pro_id="+pro_id+" and sc_id="+sc_id;  
			 getSession().beginTransaction();
			 Query queryObject = getSession().createQuery(hql);
			 List <Object>list = queryObject.list();
			 getSession().getTransaction().commit();
			 Provider_servicecatalog psc=(Provider_servicecatalog)list.get(0);
		     psc.setPrice(price);
			return update(psc);
			} catch (RuntimeException re) {
				throw re;
				
			}
	}

	public void catalogPrice() {
		Transaction tx = null;
		System.out.println("1");
		try {
			System.out.println("1.5");
			tx=getSession().beginTransaction();
			System.out.println("2");
			String queryString = "from Servicecatalog as servicecatalogs ";
			Query queryObject = getSession().createQuery(queryString);
			List <Object>list = queryObject.list();
			if (list.size() == 0) {
				System.out.println("没有服务种类");
				return;
			}
			
			for(int i=0;i<list.size();i++)
			{
				Servicecatalog servicecatalog=(Servicecatalog)list.get(i);
				int sc_id=servicecatalog.getId();//先得到每一个服务种类的id
				//在所有provider_servicecatalog中查找id为sc_id的数据并统计平均价格
				String queryString2 = "select AVG(price) from Provider_servicecatalog as average where average.servicecatalog.id=" + sc_id;
				Query queryObject2 = getSession().createQuery(queryString2);
				List <Object>list2 = queryObject2.list();
				if(list2.get(0)==null){
					continue;
				}
				float newPrice=Float.parseFloat(list2.get(0).toString());
				servicecatalog.setPrice(newPrice);
				getSession().update(servicecatalog);//更新服务种类的市场价格
			}
			tx.commit();
			System.out.println("服务种类市场价格已更新");
		} catch (Exception re) {
			tx.rollback();
			re.printStackTrace();
		}
		}
	}
	
	

